package excel

import (
	"fmt"
	"github.com/xuri/excelize/v2"
	"log"
	"os"
	"strconv"
	"time"
)

func getLogger(fileName string) *log.Logger {

	path := "./" + fileName + ".log"
	//file, err := os.OpenFile(path, os.O_CREATE|os.O_RDWR|os.O_APPEND, os.ModeAppend|os.ModePerm)
	file, err := os.Create(path)
	if err != nil {
		fmt.Println("创建失败:" + err.Error())
	}

	logger := log.New(file, "", log.LstdFlags)
	return logger
}

func getColumnArr() []string {
	a := 'A'
	res := make([]string, 0)
	for i := 0; i < 26; i++ {
		res = append(res, string(a + int32(i)))
	}
	res = append(res, "AA", "AB", "AC", "AD", "AE")
	return res
}


func ReadExcel() (hourData [][]string) {
	logFile := fmt.Sprintf("%s_%d", time.Now().Format("2006-01-02_15_04_05"), time.Now().UnixNano())
	logger := getLogger(logFile)
	f, err := excelize.OpenFile("report.xlsx")
	if err != nil {
		logger.Println(err)
		return
	}
	defer func() {
		// Close the spreadsheet.
		if err := f.Close(); err != nil {
			logger.Println(err)
		}
	}()

	rows, err := f.GetRows("Sheet1")
	if err != nil {
		logger.Println(err)
		return
	}
	hourData = make([][]string, 0)
	for i, row := range rows {
		hourRow := make([]string, 0)
		//fmt.Println(i)
		if i > 4 && i % 2 == 1 {
			for _, colCell := range row {
				hour := getWorkHour(colCell)
				hourRow = append(hourRow, fmt.Sprintf("%.1f", hour))
				//fmt.Print(j, colCell, "\t")
				//logger.Print(colCell, "\t")
			}
			hourData = append(hourData, hourRow)
		} else {
			hourData = append(hourData, row)
		}
	}
	logger.Println(hourData)
	return hourData
}

func WriteExcel() {
	data := ReadExcel()
	excelFile := fmt.Sprintf("%s_%d.xlsx", "result", time.Now().UnixNano())
	file := excelize.NewFile()
	index := file.NewSheet("Sheet1")
	abcArr := getColumnArr()
	for i, row := range data {
		for j, col := range row {
			cellFlag := abcArr[j] + strconv.Itoa(i+1)
			err := file.SetCellValue("Sheet1", cellFlag, col)
			if err != nil {
				fmt.Println(err)
			}
		}
	}
	file.SetActiveSheet(index)
	if err := file.SaveAs(excelFile); err != nil {
		fmt.Println(err)
	}
}

